package ro.imc.andrei.ums;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import javax.swing.JDialog;
import javax.swing.JOptionPane;

public class UMSDB {
	private static UMSDB DbInstance;
	
//	private static final String DERBY_DRIVER = "org.apache.derby.jdbc.EmbeddedDriver";
//	private static final String DATABASE_URL = "jdbc:derby:Databases/DatabaseViewer/connections;create=true;user=connections;password=connections";
	public static final String DATABASE_URL = "jdbc:mysql://localhost:3306/umsdb?user=root";
	private Connection con;
	
	private UMSDB() {
	}

	public static UMSDB getInstance() {
		if (DbInstance == null)
			DbInstance = new UMSDB();
		return DbInstance;
	}

//	public void checkDB() {
//		try {						
//			Class.forName("com.mysql.jdbc.Driver");
//			Connection con = DriverManager.getConnection(DATABASE_URL);
			
//			List<String> statments = getSQL();
//			
//			Statement stmt = null;
//			for (String sqlStatement : statments) {
//				if (!sqlStatement.trim().isEmpty()) {
//					System.out.println(sqlStatement);
//					stmt = con.createStatement();
//					stmt.executeUpdate(sqlStatement.trim());
//				}
//			}
//			
//			con.close();
//			System.out.println("Database created successfully");
//		}
//		catch (SQLException se){
//			se.printStackTrace();
//			JOptionPane pane = new JOptionPane("SQL Exception",
//					JOptionPane.ERROR_MESSAGE, JOptionPane.DEFAULT_OPTION);
//				JDialog dialog = pane.createDialog(null, "Error!");
//				dialog.setVisible(true);
//		}
//		catch (Exception e) {
//			e.printStackTrace();
//			JOptionPane pane = new JOptionPane(e.getMessage() + " " + e.getClass(),
//				JOptionPane.ERROR_MESSAGE, JOptionPane.DEFAULT_OPTION);
//			JDialog dialog = pane.createDialog(null, "Error!");
//			dialog.setVisible(true);
//		}
//	}
	
//	private List<String> getSQL(){
//		List<String> sqlQueries= new ArrayList<String>();
//		
//		sqlQueries.add("CREATE TABLE student (studentId INT(10) AUTO_INCREMENT NOT NULL, firstName VARCHAR(20), lastName VARCHAR(20), id INT(3), studentNo INT(3), status VARCHAR(50), primary key (studentID), foreign key (groupId) REFERENCES groups(groupId))");
//		sqlQueries.add("CREATE TABLE professor (professorId INT(10) AUTO_INCREMENT NOT NULL, firstName VARCHAR(20), lastName VARCHAR(20), id INT(3), title VARCHAR(50), field VARCHAR(50), primary key (professorId))");
//		sqlQueries.add("CREATE TABLE course (courseId INT(10) AUTO_INCREMENT NOT NULL, courseName VARCHAR(20), mandatory VARCHAR(20), courseId INT(3), semester int(3), primary key (courseId))");
//		sqlQueries.add("CREATE TABLE groups (groupId INT(10) AUTO_INCREMENT NOT NULL, groupName VARCHAR(20), yearOfStudy INT(3), fieldOfStudy INT(3), primary key (groupId))");
//	
//		return sqlQueries;
//	}

	public void insertMethod(String table, String[] fields, String[] values) {
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(DATABASE_URL);
		
			String insertQuery = "INSERT INTO " + table + "(";
			String fieldsQuery = "";
			String valuesQuery = "'";
	
			// Construct Fields Query and Values Query
			for (int i = 0; i < fields.length - 1; i++) {
				fieldsQuery = fieldsQuery + fields[i] + ", ";
				valuesQuery = valuesQuery + values[i] + "', '";
			}
			fieldsQuery = fieldsQuery + fields[fields.length - 1] + ") VALUES (";
			valuesQuery = valuesQuery + values[values.length - 1] + "')";
	
			// Construct Final Insert Query
			insertQuery = insertQuery + fieldsQuery + valuesQuery;
			
			System.out.println(insertQuery);
			
			//Execute Statement and close connection
			Statement stmt = con.createStatement();
			stmt.executeUpdate(insertQuery);
			
			stmt.close();
			con.close();
		}
		catch(NullPointerException npe){
			npe.printStackTrace();
			JOptionPane pane = new JOptionPane("NULL Value Found! Check Values!",
				JOptionPane.ERROR_MESSAGE, JOptionPane.DEFAULT_OPTION);
			JDialog dialog = pane.createDialog(null, "Error!");
			dialog.setVisible(true);
		}
		catch(SQLException sqle){
			sqle.printStackTrace();
			JOptionPane pane = new JOptionPane("SQL Exception Occured! Check Values",
				JOptionPane.ERROR_MESSAGE, JOptionPane.DEFAULT_OPTION);
			JDialog dialog = pane.createDialog(null, "Error!");
			dialog.setVisible(true);
		}
		catch(Exception e){
			e.printStackTrace();
			JOptionPane pane = new JOptionPane("Exception Found! Check Values!",
				JOptionPane.ERROR_MESSAGE, JOptionPane.DEFAULT_OPTION);
			JDialog dialog = pane.createDialog(null, "Error!");
			dialog.setVisible(true);
		}
	}

	public void updateMethod(String table, String[] updateFields,String[] updateValues, String searchField, String searchValue) {
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(DATABASE_URL);
			
			String updateQuery = "UPDATE " + table + " SET ";
			String setQuery = "";
	
			// Construct Set Query
			for (int i = 0; i < updateFields.length - 1; i++) {
				setQuery = setQuery + updateFields[i] + "='" + updateValues[i]
						+ "',";
			}
			setQuery = setQuery + updateFields[updateFields.length - 1] + "='"
					+ updateValues[updateValues.length - 1] + "'";
	
			updateQuery = updateQuery + setQuery + " WHERE " + searchField + " = '"
					+ searchValue + "'";
	
			System.out.println(updateQuery);
			
			//Execute Statement and close connection
			Statement stmt = con.createStatement();
			stmt.executeUpdate(updateQuery);
			
			stmt.close();
			con.close();
		}
		catch(NullPointerException npe){
			npe.printStackTrace();
			JOptionPane pane = new JOptionPane("NULL Value Found! Check Values!",
				JOptionPane.ERROR_MESSAGE, JOptionPane.DEFAULT_OPTION);
			JDialog dialog = pane.createDialog(null, "Error!");
			dialog.setVisible(true);
		}
		catch(SQLException sqle){
			sqle.printStackTrace();
			JOptionPane pane = new JOptionPane("SQL Exception Occured! Check Values",
				JOptionPane.ERROR_MESSAGE, JOptionPane.DEFAULT_OPTION);
			JDialog dialog = pane.createDialog(null, "Error!");
			dialog.setVisible(true);
		}
		catch(Exception e){
			e.printStackTrace();
			JOptionPane pane = new JOptionPane("Exception Found! Check Values!",
				JOptionPane.ERROR_MESSAGE, JOptionPane.DEFAULT_OPTION);
			JDialog dialog = pane.createDialog(null, "Error!");
			dialog.setVisible(true);
		}
	}

	public void deleteMethod(String table, String field, String value) {
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(DATABASE_URL);
			
			// Construct Delete Query
			String query = "DELETE FROM " + table + " WHERE " + field + " = '"
					+ value + "'";
	
			System.out.println(query);
			
			//Execute Statement and close connection
			Statement stmt = con.createStatement();
			stmt.executeUpdate(query);
			
			stmt.close();
			con.close();
		}
		catch(NullPointerException npe){
			npe.printStackTrace();
			JOptionPane pane = new JOptionPane("NULL Value Found! Check Values!",
				JOptionPane.ERROR_MESSAGE, JOptionPane.DEFAULT_OPTION);
			JDialog dialog = pane.createDialog(null, "Error!");
			dialog.setVisible(true);
		}
		catch(SQLException sqle){
			sqle.printStackTrace();
			JOptionPane pane = new JOptionPane("SQL Exception Occured! Check Values",
				JOptionPane.ERROR_MESSAGE, JOptionPane.DEFAULT_OPTION);
			JDialog dialog = pane.createDialog(null, "Error!");
			dialog.setVisible(true);
		}
		catch(Exception e){
			e.printStackTrace();
			JOptionPane pane = new JOptionPane("Exception Found! Check Values!",
				JOptionPane.ERROR_MESSAGE, JOptionPane.DEFAULT_OPTION);
			JDialog dialog = pane.createDialog(null, "Error!");
			dialog.setVisible(true);
		}
	}
}
